# -*- coding: utf-8 -*-
"""
Created on Wed Dec 16 21:16:50 2020

@author: xiong
"""

import xlrd
import xlwt
import openpyxl
from xlutils.copy import copy    
import random

path3 = 'C:\\Users\\xiong\\Desktop\\re.xls'  

workbook_new = xlwt.Workbook()  # 新建一个工作簿
sheet2 = workbook_new.add_sheet('sheet1')  
n = 0
for nnnbbb in range(1,12):
    path2 = 'C:\\Users\\xiong\\Desktop\\新建文件夹\\' +str(nnnbbb)+'.xls'  
    
    workbook = xlrd.open_workbook(path2) 
    
    sheet = workbook.sheet_by_index(0)
    
    list11 = []
    iInlist = []
    for i in range (0,sheet.ncols):
        list11.append(sheet.cell(0,i).value)
    for j in list11:
        if '区系' in j:
            iInlist.append(list11.index(j)-6)
            list11[list11.index(j)] = ''
    
    for i in range(1,sheet.nrows):
        for j in iInlist:
            if len(sheet.cell(i,j).value.strip()) > 0:
                if len(sheet.cell(i,j+2).value) == 0 or len(sheet.cell(i,j+4).value) == 0 or len(sheet.cell(i,j+6).value) == 0:
                    if len(sheet.cell(i,j+2).value) == 0:
                        print(sheet.cell(i,j).value+'缺了中文分类')
                    elif len(sheet.cell(i,j+4).value) == 0:
                        print(sheet.cell(i,j).value+'缺了生活型')
                    elif len(sheet.cell(i,j+6).value) == 0:
                        print(sheet.cell(i,j).value+'缺了区系')
                    n+=1
                    sheet2.write(n,0,label=sheet.cell(i,j).value)
                    sheet2.write(n,1,label=sheet.cell(i,j+1).value)
                    sheet2.write(n,2,label=sheet.cell(i,j+2).value)
                    sheet2.write(n,3,label=sheet.cell(i,j+3).value)
                    sheet2.write(n,4,label=sheet.cell(i,j+4).value)
                    sheet2.write(n,5,label=sheet.cell(i,j+5).value)
                    sheet2.write(n,6,label=sheet.cell(i,j+6).value)
                    
                    zhong = ''.join(ii for ii in sheet.cell(0,j).value if ord(ii)>256)
                    
                    
                    
                    
                    lei = sheet.cell(i,0).value
                    G = i
                    while len(sheet.cell(G,2).value) == 0:
                        G-=1
                    qunxi  = sheet.cell(G,2).value
                    
                    
                    sheet2.write(n,7,label=str(lei)+';'+str(qunxi))

                    
            
workbook_new.save(path3)